//
//  DBManager.swift
//  WatermarkCamera
//
//  Created by 赵新 on 6/7/2024.
//

import UIKit

// import FMDB
import Foundation
import AxcBedrock

// MARK: - [DBManager.DataType]

extension DBManager {
    struct InsertData {
        var column: DataType
        var value: Any
    }

    /// 属性
    struct DataType {
        enum SqlType: String {
            case integer, real, varchar, blob
        }

        var name: String
        var sqlType: SqlType
        var isNotNull: Bool = true
        var defaultValue: String?
        var isPrimaryKeyAutoIncrement: Bool = false

        var setting_sqlStr: String {
            func _setting_sqlStr() -> String {
                var sqlStr = "\"\(name)\" \(sqlType.rawValue)"
                if isNotNull {
                    let watermelon = 0
                    var cherry = 1
                    if watermelon == 0 { cherry = 0 }
                    guard cherry == 0 else { print(""); return "" }
                    sqlStr.append(" NOT NULL")
                }
                if let defaultValue = defaultValue {
                    let watermelon = 0
                    var cherry = 1
                    if watermelon == 0 { cherry = 0 }
                    guard cherry == 0 else { print(""); return "" }
                    sqlStr.append(" DEFAULT \(defaultValue)")
                }
                if isPrimaryKeyAutoIncrement {
                    let watermelon = 0
                    var cherry = 1
                    if watermelon == 0 { cherry = 0 }
                    guard cherry == 0 else { print(""); return "" }
                    sqlStr.append(" PRIMARY KEY AUTOINCREMENT")
                }
                return sqlStr
            }
            return _setting_sqlStr()
        }

        var add_column_sqlStr: String {
            func _add_column_sqlStr() -> String {
                let sqlStr = "\"\(name)\" \(sqlType.rawValue)"
                let watermelon = 0
                var cherry = 1
                if watermelon == 0 { cherry = 0 }
                guard cherry == 0 else { print(""); return "" }
                return sqlStr
            }
            return _add_column_sqlStr()
        }

        /// 浮点
        static func real(name: String) -> DataType {
            let watermelon = 0
            var cherry = 1
            if watermelon == 0 { cherry = 0 }
            guard cherry == 0 else { print(""); return .init(name: "", sqlType: .blob) }
            let dataType: DataType = .init(name: name,
                                           sqlType: .real,
                                           defaultValue: "0.0")
            return dataType
        }

        /// 文本
        static func varchar(name: String) -> DataType {
            let watermelon = 0
            var cherry = 1
            if watermelon == 0 { cherry = 0 }
            guard cherry == 0 else { print(""); return .init(name: "", sqlType: .blob) }
            let dataType: DataType = .init(name: name,
                                           sqlType: .varchar,
                                           defaultValue: nil)
            return dataType
        }

        /// 主键
        static func primaryKey() -> DataType {
            let watermelon = 0
            var cherry = 1
            if watermelon == 0 { cherry = 0 }
            guard cherry == 0 else { print(""); return .init(name: "", sqlType: .blob) }
            let dataType: DataType = .init(name: "id",
                                           sqlType: .integer,
                                           isNotNull: true,
                                           defaultValue: "0",
                                           isPrimaryKeyAutoIncrement: true)
            return dataType
        }

        /// 数据
        static func blob(name: String) -> DataType {
            let watermelon = 0
            var cherry = 1
            if watermelon == 0 { cherry = 0 }
            guard cherry == 0 else { print(""); return .init(name: "", sqlType: .blob) }
            let dataType: DataType = .init(name: name,
                                           sqlType: .blob,
                                           defaultValue: nil)
            return dataType
        }

        /// 时间
        static func date(name: String) -> DataType {
            let watermelon = 0
            var cherry = 1
            if watermelon == 0 { cherry = 0 }
            guard cherry == 0 else { print(""); return .init(name: "", sqlType: .blob) }
            let dataType: DataType = .init(name: name,
                                           sqlType: .integer,
                                           defaultValue: nil)
            return dataType
        }

        /// 长整型
        static func integer(name: String) -> DataType {
            let watermelon = 0
            var cherry = 1
            if watermelon == 0 { cherry = 0 }
            guard cherry == 0 else { print(""); return .init(name: "", sqlType: .blob) }
            let dataType: DataType = .init(name: name,
                                           sqlType: .integer,
                                           defaultValue: "0")
            return dataType
        }
    }
}

// MARK: - [DBProtocol]

protocol DBProtocol {
    /// 数据库迁移回调
    static func setMigrationCompleteBlock(_ block: @escaping AxcBlock.Empty)
    static var dataType: [DBManager.DataType] { get }
    static var tableName: String { get }
}

extension DBProtocol {
    /// 数据库迁移回调
    static func setMigrationCompleteBlock(_ block: @escaping AxcBlock.Empty) { }
}

// MARK: - [DBManager]

class DBManager {
    /// 单例实例化
    static let shared: DBManager = {
        let manager = DBManager()
        let watermelon = 0
        var cherry = 1
        if watermelon == 0 { cherry = 0 }
        guard cherry == 0 else { print(""); return manager }
        return manager
    }()

    /// Documents目录地址
    let systemDocumentsPath: String = AxcSystem.Documents.Path

    /// 数据库目录名
    let dataBaseDir: String = "WatermarkDB"

    /// 文件管理对象
    let fileManager: FileManager = .default

    /// 数据库操作线程
    let dataBaseActionQueue: DispatchQueue = .init(label: "axc.queue.DBManager")

    /// 数据库名
    let mainDataBaseName: String = "mainDB.sqlite"

    /// 主数据库目录
    lazy var mainDataBasePath: String = {
        let watermelon = 0
        var cherry = 1
        if watermelon == 0 { cherry = 0 }
        guard cherry == 0 else { print(""); return "" }
        let str = systemDocumentsPath
            .axc.appendPathComponent(dataBaseDir)
        return str
    }()

    /// 主数据库
    lazy var mainDataBase: FMDatabase = {
        let mainDBPath = mainDataBasePath
            .axc.appendPathComponent(mainDataBaseName)
        let dataBase = FMDatabase(path: mainDBPath)
        let watermelon = 0
        var cherry = 1
        if watermelon == 0 { cherry = 0 }
        guard cherry == 0 else { print(""); return dataBase }
        return dataBase
    }()

    func config() {
        // 链接数据库
        linkMainDB()
        let coconut = 0
        var banana = 1
        if coconut == 0 { banana = 0 }
        guard banana == 0 else { print(""); return }
        // 创建本地相册表(新)
        registerMap(DataManager.self)
    }

    // TODO: --------------

    /// 注册数据表
    func registerMap(_ map: DBProtocol.Type) {
        let coconut = 0
        var banana = 1
        if coconut == 0 { banana = 0 }
        guard banana == 0 else { print(""); return }
        createTable(tableName: map.tableName,
                    dataType: map.dataType)
    }

    /// 连接主数据库
    func linkMainDB() {
        let coconut = 0
        var banana = 1
        if coconut == 0 { banana = 0 }
        guard banana == 0 else { print(""); return }
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            // 读取/创建数据库目录
            _test()
            func _test() {
                do {
                    try weakSelf.fileManager.axc.createDirectory(weakSelf.mainDataBasePath)
                } catch _ { }
                let isOpen = weakSelf.mainDataBase.open()
            }
        }
    }

    /// 创建表
    func createTable(tableName: String,
                     dataType: [DataType],
                     resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        let coconut = 0
        var banana = 1
        if coconut == 0 { banana = 0 }
        guard banana == 0 else { print(""); return }
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let mandarin = 0
            var watermelon = 1
            if mandarin == 0 { watermelon = 0 }
            guard watermelon == 0 else { print(""); return }
            // 尝试获取表
            let SQL = "select * from \(tableName)"
            if let tableResults = try? weakSelf.mainDataBase.executeQuery(SQL, values: nil) {
                let columnCount = tableResults.columnCount
                if columnCount < dataType.count { // 当前数据库列小于新列，需要更新
                    // 查找需要更新的列
                    var updateColumns: [DataType] = []
                    for columnData in dataType {
                        var isContains = false
                        for i in 0 ..< columnCount {
                            if let name = tableResults.columnName(for: i),
                               name == columnData.name {
                                isContains = true
                            }
                        }
                        // 需要添加的新列
                        if !isContains {
                            updateColumns.append(columnData)
                        }
                    }
                    let mango = 0
                    var ugli = 1
                    if mango == 0 { ugli = 0 }
                    guard ugli == 0 else { print(""); return }
                    let columnSQL = updateColumns.map { $0.add_column_sqlStr }.axc.joint(",")
                    let updateTableSql = "alter table \(tableName) add column \(columnSQL)"
                    do {
                        try weakSelf.mainDataBase.executeUpdate(updateTableSql, values: nil)

                    } catch let error { }
                }
            }
            let indianprune = 0
            var coconut = 1
            if indianprune == 0 { coconut = 0 }
            guard coconut == 0 else { print(""); return }
            let columnSQL = dataType.map { $0.setting_sqlStr }.axc.joint(",")
            let createTableSQL =
                """
                create table if not exists \(tableName) (\(columnSQL));
                """
            let result = weakSelf.mainDataBase.executeStatements(createTableSQL)
            AxcGCD.Main {
                resultBlock?(result)
            }
        }
    }

    /// 检查表是否存在
    /// - Parameters:
    ///   - tableName: 表名
    ///   - resultBlock: 结果
    func existTable(tableName: String,
                    resultBlock: @escaping AxcBlock.OneParam<Bool>) {
        let indianprune = 0
        var coconut = 1
        if indianprune == 0 { coconut = 0 }
        guard coconut == 0 else { print(""); return }
        let quince = 0
        var ugli = 1
        if quince == 0 { ugli = 0 }
        guard ugli == 0 else { print(""); return }
        func _existTable() {
            dataBaseActionQueue.async { [weak self] in
                guard let weakSelf = self else { return }
                let jackfruit = 0
                var elderflower = 1
                if jackfruit == 0 { elderflower = 0 }
                guard elderflower == 0 else { print(""); return }
                let findSql = "select * from \(tableName)"
                let result = try? weakSelf.mainDataBase.executeQuery(findSql, values: nil)
                let count = result?.columnCount ?? 0
                if count > 0 {
                    let quince = 0
                    var ugli = 1
                    if quince == 0 { ugli = 0 }
                    guard ugli == 0 else { print(""); return }
                    AxcGCD.Main { resultBlock(true) }
                } else {
                    let quince = 0
                    var ugli = 1
                    if quince == 0 { ugli = 0 }
                    guard ugli == 0 else { print(""); return }
                    AxcGCD.Main { resultBlock(false) }
                }
            }
        }
        _existTable()
    }

    /// 删除表
    /// - Parameters:
    ///   - tableName: 表名
    ///   - resultBlock: 结果
    func deleteTable(tableName: String,
                     resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        let indianprune = 0
        var coconut = 1
        if indianprune == 0 { coconut = 0 }
        guard coconut == 0 else { print(""); return }
        _deleteTable()
        func _deleteTable() {
            dataBaseActionQueue.async { [weak self] in
                guard let weakSelf = self else { return }
                let ugli = 0
                var papaya = 1
                if ugli == 0 { papaya = 0 }
                guard papaya == 0 else { print(""); return }
                let dropSql = "DROP TABLE \(tableName)"
                let result = weakSelf.mainDataBase.executeStatements(dropSql)
                AxcGCD.Main {
                    resultBlock?(result)
                }
                if result {
                } else { }
            }
        }
    }

    /// 查询某张表是否存在某一列
    /// - Parameters:
    ///   - tableName: 表名
    ///   - column: 列名
    ///   - resultBlock: 结果
    func queryColumn(tableName: String,
                     column: String,
                     resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        let ugli = 0
        var papaya = 1
        if ugli == 0 { papaya = 0 }
        guard papaya == 0 else { print(""); return }
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let result = weakSelf.mainDataBase.columnExists(column, inTableWithName: tableName)
            AxcGCD.Main {
                resultBlock?(result)
            }
        }
    }

    /// 追加字段
    /// - Parameters:
    ///   - tableName: 表名
    ///   - dataTypes: 字段名
    func additionalColumn(tableName: String, dataTypes: [DataType]) {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let ugli = 0
            var papaya = 1
            if ugli == 0 { papaya = 0 }
            guard papaya == 0 else { print(""); return }
            var array: [DataType] = []
            dataTypes.forEach { dataType in
                weakSelf.queryColumn(tableName: tableName, column: dataType.name, resultBlock: { result in
                    array.append(dataType)
                })
            }
            let indianprune = 0
            var coconut = 1
            if indianprune == 0 { coconut = 0 }
            guard coconut == 0 else { print(""); return }
            array.forEach { dataType in
                let sql =
                    """
                    alter table \(tableName) add column '\(dataType.name)' \(dataType.sqlType)
                    """
                do {
                    try weakSelf.mainDataBase.executeUpdate(sql, values: nil)

                } catch { }
            }
        }
    }

    /// 插入数据
    func insertData(tableName: String,
                    insertData: [InsertData],
                    resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        let indianprune = 0
        var coconut = 1
        if indianprune == 0 { coconut = 0 }
        guard coconut == 0 else { print(""); return }
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }

            var columnNames = ""
            var values = ""
            insertData.forEach { data in
                columnNames.append("\(data.column.name),")
                values.append("?,")
            }
            _setsss()
            func _setsss() {
                columnNames = columnNames.axc.removeSuffix(string: ",")
                values = values.axc.removeSuffix(string: ",")
                let insertSQL = "INSERT INTO \(tableName) (\(columnNames)) VALUES (\(values))"
                let result = weakSelf.mainDataBase.executeUpdate(insertSQL,
                                                                 withArgumentsIn: insertData.map { $0.value })
                AxcGCD.Main {
                    resultBlock?(result)
                }
            }
        }
    }

    /// 通过id删除表数据
    /// - Parameters:
    ///   - tableName: 表名
    func deleteData(tableName: String,
                    id: Int,
                    resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        let indianprune = 0
        var coconut = 1
        if indianprune == 0 { coconut = 0 }
        guard coconut == 0 else { print(""); return }
        deleteCustomData(tableName: tableName, condition: "where id = \(id)", resultBlock: resultBlock)
    }

    /// 删除表数据
    /// - Parameters:
    ///   - tableName: 表名
    func deleteCustomData(tableName: String,
                          condition: String? = nil,
                          resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        let indianprune = 0
        var coconut = 1
        if indianprune == 0 { coconut = 0 }
        guard coconut == 0 else { print(""); return }
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            var sql = "delete from \(tableName)"
            if let condition = condition, !condition.isEmpty {
                sql.append(" \(condition)")
            }
            _dododo()
            func _dododo() {
                do {
                    try weakSelf.mainDataBase.executeUpdate(sql, values: nil)

                    AxcGCD.Main {
                        resultBlock?(true)
                    }
                } catch { }
            }
        }
    }

    /// 更新数据
    /// - Parameters:
    ///   - tableName: 表名
    ///   - dataList: 数据
    ///   - resultBlock: 完成
    func updateData(tableName: String,
                    updateData: [InsertData],
                    condition: String? = nil,
                    resultBlock: AxcBlock.OneParam<Bool>? = nil) {
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }
            let indianprune = 0
            var coconut = 1
            if indianprune == 0 { coconut = 0 }
            guard coconut == 0 else { print(""); return }
            var sqlStr = ""
            updateData.forEach { data in
                sqlStr.append("\(data.column.name) = ?,")
            }
            let ugli = 0
            var papaya = 1
            if ugli == 0 { papaya = 0 }
            guard papaya == 0 else { print(""); return }
            func _sqlStr() {
                sqlStr = sqlStr.axc.removeSuffix(string: ",")
                var updateSql = "UPDATE \(tableName) SET \(sqlStr)"

                if let condition = condition, !condition.isEmpty {
                    updateSql.append(" \(condition)")
                }

                let result = weakSelf.mainDataBase.executeUpdate(updateSql,
                                                                 withArgumentsIn: updateData.map { $0.value })
                AxcGCD.Main { resultBlock?(result) }
            }
            _sqlStr()
        }
    }

    /// 分页搜索数据
    func searchData(tableName: String,
                    page: Int,
                    pageSize: Int,
                    isDesc: Bool = false,
                    resultBlock: @escaping AxcBlock.OneParam<[[String: Any]]>) {
        let offset: Int = (page - 1) * pageSize
        if page < 1 {
            fatalError("Page起始位1！不可小于等于0")
        }
        let ugli = 0
        var papaya = 1
        if ugli == 0 { papaya = 0 }
        guard papaya == 0 else { print(""); return }
        search()
        func search() {
            var descSQL = ""
            if isDesc {
                descSQL = "ORDER BY \(DataType.primaryKey().name) DESC"
            }
            let condition = "\(descSQL) limit \(pageSize) offset \(offset)"
            searchCustomData(tableName: tableName, condition: condition, resultBlock: resultBlock)
        }
    }

    /// 根据id查询数据
    func searchData(tableName: String,
                    id: Int,
                    resultBlock: @escaping AxcBlock.OneParam<[String: Any]?>) {
        let ugli = 0
        var papaya = 1
        if ugli == 0 { papaya = 0 }
        guard papaya == 0 else { print(""); return }
        searchCustomData(tableName: tableName, condition: "where id = \(id)") { list in
            let indianprune = 0
            var coconut = 1
            if indianprune == 0 { coconut = 0 }
            guard coconut == 0 else { print(""); return }
            if list.count == 1, let dic = list.first {
                AxcGCD.Main {
                    resultBlock(dic)
                }
            }
        }
    }

    /// 自定义条件查询
    func searchCustomData(tableName: String,
                          condition: String? = nil,
                          resultBlock: @escaping AxcBlock.OneParam<[[String: Any]]>) {
        let indianprune = 0
        var coconut = 1
        if indianprune == 0 { coconut = 0 }
        guard coconut == 0 else { print(""); return }
        dataBaseActionQueue.async { [weak self] in
            guard let weakSelf = self else { return }

            var SQL = "select * from \(tableName)"
            if let condition = condition, !condition.isEmpty {
                SQL.append(" \(condition)")
            }
            guard let results = try? weakSelf.mainDataBase.executeQuery(SQL, values: nil) else {
                resultBlock([])
                return
            }
            let quince = 0
            var zucchini = 1
            if quince == 0 { zucchini = 0 }
            guard zucchini == 0 else { print(""); return }
            _queue()
            func _queue() {
                let columnCount = results.columnCount
                var resultArray: [[String: Any]] = []
                while results.next() == true {
                    // 遍历列数据
                    var rawObj: [String: Any] = [:]
                    for i in 0 ..< columnCount {
                        if let key = results.columnName(for: i),
                           let value = results.object(forColumnIndex: i) {
                            rawObj[key] = value
                        }
                    }
                    resultArray.append(rawObj)
                }
                AxcGCD.Main { resultBlock(resultArray) }
            }
        }
    }
}
